/****** Object:  Table [dbo].[Data]    Script Date: 12/05/2007 23:31:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Data](
	[DataID] [int] IDENTITY(1,1) NOT NULL,
	[ApplicationID] [uniqueidentifier] NOT NULL,
	[DataName] [nvarchar](512) NOT NULL,
	[ClassName] [nvarchar](512) NULL,
	[DataContent] [varbinary](max) NULL,
	[LastUpdate] [timestamp] NULL,
	[Locked] [bit] NULL CONSTRAINT [DF_Data_Lock]  DEFAULT ((0)),
	[ServerDnsName] [nvarchar](512) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  StoredProcedure [dbo].[StoreData]    Script Date: 12/05/2007 23:31:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Error Code
-- 0 ok
-- 1 Locked
-- 2 Not Same Class Name

CREATE PROCEDURE [dbo].[StoreData]
(
	@ApplicationID		UNIQUEIDENTIFIER,
	@DataName			NVARCHAR(512),
	@ClassName			NVARCHAR(512),
	@DataContent		VARBINARY(MAX),
	@ServerDnsName		NVARCHAR(512),
	@IsLocked			BIT OUTPUT,
	@LastUpdate			TIMESTAMP OUTPUT
)
AS
BEGIN

	DECLARE @Res INT
	DECLARE @DataID INT
	SET @IsLocked = 0

	SELECT @DataID = [DataID]  FROM [dbo].[Data] WHERE [DataName] = @DataName AND [ApplicationID] = @ApplicationID

	IF @DataID IS NULL
		BEGIN
			INSERT INTO [Data]
					([ApplicationID]
					,[DataName]
					,[ClassName]
					,[DataContent]
					,[ServerDnsName])
			VALUES
				   (@ApplicationID,
					@DataName,
					@ClassName,
					@DataContent,
					@ServerDnsName)
			
			SET @DataID = @@IDENTITY

			SELECT @LastUpdate =	LastUpdate
									FROM [dbo].[Data]
									WHERE [DataID] = @DataID
			SET @Res = 0 -- OK
		END
	ELSE
		BEGIN
			DECLARE @DbLock bit
			DECLARE @DbClassName NVARCHAR(512)
			DECLARE @DbServerName NVARCHAR(512)
			
			SELECT @DbLock =	[Locked]
								FROM [dbo].[Data] 
								WHERE [DataID] = @DataID

			SELECT @DbClassName =	[ClassName] 
									FROM [dbo].[Data] 
									WHERE [DataID] = @DataID

			SELECT @DbServerName=	[ServerDnsName] 
									FROM [dbo].[Data] 
									WHERE [DataID] = @DataID
			
			IF(@DbLock = 0)
				BEGIN
					IF @DbClassName = @ClassName
						BEGIN
							UPDATE [Data]
							SET  [DataContent]	=	@DataContent
								,[ServerDnsName]	=	@ServerDnsName
							WHERE [DataID]		=	@DataID
							
							SELECT @LastUpdate =	LastUpdate
													FROM [dbo].[Data]
													WHERE [DataID] = @DataID
							SET @Res = 0 -- OK
						END
					ELSE
						BEGIN
							SET @Res =  2 -- Not Same Class Name Error Code
						END
				END
			ELSE
				BEGIN
					IF @DbServerName = @DbServerName
						BEGIN
							IF @DbClassName = @ClassName
								BEGIN
									UPDATE [Data]
									SET  [DataContent] =	@DataContent
										,[ServerDnsName] =		@ServerDnsName
									WHERE [DataID] =		@DataID

									SELECT @LastUpdate =	LastUpdate
															FROM [dbo].[Data]
															WHERE [DataID] = @DataID
									SET @IsLocked = 1
									SET @Res = 0 -- OK
								END
							ELSE
								BEGIN
									SET @Res =  2 -- Not Same Class Name Error Code
								END
						END
					ELSE
						BEGIN
							SET @Res = 1 -- Locked Error Code
						END
				END
	END
	RETURN @Res
END
GO
/****** Object:  StoredProcedure [dbo].[LoadData]    Script Date: 12/05/2007 23:31:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Error Code
-- 0 ok
-- 1 Locked
-- 2 Not Same Class Name
-- 3 Does't Exist

CREATE PROCEDURE [dbo].[LoadData]
(
	@ApplicationID		UNIQUEIDENTIFIER,
	@DataName		NVARCHAR(512),
	@ClassName			NVARCHAR(512) = NULL,
	@DataContent	VARBINARY(MAX) output,
	@ServerDnsName		NVARCHAR(512),
	@LockedByServer			BIT OUTPUT,
	@LastUpdate			TIMESTAMP output
)
AS
BEGIN
	DECLARE @Res INT
	DECLARE @DataID INT
	
	SELECT @DataID =	[DataID]  
							FROM [dbo].[Data] 
							WHERE [DataName] = @DataName AND [ApplicationID] = @ApplicationID


	IF @DataID IS NULL
		BEGIN
			SET @Res = 3 -- Doesn't Exist
		END
	ELSE
		BEGIN
			DECLARE @DbClassName		NVARCHAR(512)
			DECLARE @DbServerDnsName	NVARCHAR(512)
			DECLARE @Locked				BIT

			IF(@ClassName IS NULL)
				BEGIN
					SELECT @DataContent =	DataContent 
												FROM [dbo].[Data] 
												WHERE [DataID] = @DataID
					
					SELECT @LastUpdate		=	LastUpdate
												FROM [dbo].[Data] 
												WHERE [DataID] = @DataID
					
					SELECT @Locked			=	Locked
												FROM [dbo].[Data] 
												WHERE [DataID] = @DataID
					IF @Locked = 1
						BEGIN
							SELECT @DbServerDnsName =	ServerDnsName 
														FROM [dbo].[Data] 
														WHERE [DataID] = @DataID
							IF @DbServerDnsName = @ServerDnsName
								BEGIN
									SET @LockedByServer = 1
								END
							ELSE
								BEGIN
									SET @LockedByServer = 0
								END
						END
					ELSE
						BEGIN
							SET @LockedByServer = 0
						END

					SET @Res =  0 -- OK
				END
			ELSE
				BEGIN
					SELECT @DbClassName =	ClassName 
											FROM [dbo].[Data] 
											WHERE [DataID] = @DataID
					IF @DbClassName = @ClassName
						BEGIN
							SELECT @DataContent =	DataContent 
														FROM [dbo].[Data] 
														WHERE [DataID] = @DataID
							
							SELECT @LastUpdate =	LastUpdate
														FROM [dbo].[Data] 
														WHERE [DataID] = @DataID

							IF @Locked = 1
								BEGIN
									SELECT @DbServerDnsName =	ServerDnsName 
																FROM [dbo].[Data] 
																WHERE [DataID] = @DataID
									IF @DbServerDnsName = @ServerDnsName
										BEGIN
											SET @LockedByServer = 1
										END
									ELSE
										BEGIN
											SET @LockedByServer = 0
									END
								END
							ELSE
								BEGIN
									SET @LockedByServer = 0
								END
							SET @Res = 0 -- OK
						END
					ELSE
						BEGIN
							SET @Res = 2 -- Not Same Class Name Error Code
						END
				END
		END
	RETURN @Res
END
GO
/****** Object:  StoredProcedure [dbo].[RemoveData]    Script Date: 12/05/2007 23:31:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Error Code
-- 0 ok
-- 1 Locked
-- 2 Not Same Class Name
-- 3 Does't Exist

CREATE PROCEDURE [dbo].[RemoveData]
(
	@ApplicationID		UNIQUEIDENTIFIER,
	@DataName		NVARCHAR(512),
	@ServerDnsName		NVARCHAR(512)
)
AS
BEGIN

	DECLARE @Res INT
	DECLARE @DataID INT

	SELECT @DataID = [DataID]  FROM [dbo].[Data] WHERE [DataName] = @DataName AND [ApplicationID] = @ApplicationID

	IF @DataID IS NULL
		BEGIN
			SET @Res = 0 -- OK
		END
	ELSE
		BEGIN
			DECLARE @DbLock bit
			DECLARE @DbClassName NVARCHAR(512)
			DECLARE @DbServerName NVARCHAR(512)
			
			SELECT @DbLock =	[Locked]
								FROM [dbo].[Data] 
								WHERE [DataID] = @DataID


			SELECT @DbServerName=	[ServerDnsName] 
									FROM [dbo].[Data] 
									WHERE [DataID] = @DataID
			
			IF(@DbLock = 0)
				BEGIN
					DELETE FROM [dbo].[Data]
					WHERE [DataID] = @DataID
		
					SET @Res = 0 -- OK
				END
			ELSE
				BEGIN
					IF @DbServerName = @DbServerName
						BEGIN
							DELETE FROM [dbo].[Data]
							WHERE [DataID] = @DataID
				
							SET @Res = 0 -- OK
						END
					ELSE
						BEGIN
							SET @Res = 1 -- Locked Error Code
						END
				END
	END
	RETURN @Res
END
GO
/****** Object:  StoredProcedure [dbo].[LockData]    Script Date: 12/05/2007 23:31:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Error Code
-- 0 ok
-- 1 Locked
-- 2 Not Same Class Name
-- 3 Does't Exist

CREATE PROCEDURE [dbo].[LockData]
(
	@ApplicationID		UNIQUEIDENTIFIER,
	@DataName		NVARCHAR(512),
	@ServerDnsName		NVARCHAR(512),
	@LockWaitTime		INT	= 50
)
AS
BEGIN

	DECLARE @Res INT
	DECLARE @DataID INT

	SELECT @DataID = [DataID]  FROM [dbo].[Data] WHERE [DataName] = @DataName AND [ApplicationID] = @ApplicationID

	IF @DataID IS NULL
		BEGIN
			SET @Res = 3 -- Doesn't Exists
		END
	ELSE
		BEGIN
			DECLARE @DbLock bit
			DECLARE @DbClassName NVARCHAR(512)
			DECLARE @DbServerName NVARCHAR(512)
			
			SELECT @DbServerName=	[ServerDnsName] 
									FROM [dbo].[Data] 
									WHERE [DataID] = @DataID

			DECLARE @Count INT
			SET	@Count = @LockWaitTime 
Reload:
			BEGIN TRANSACTION 
			SELECT @DbLock =	[Locked]
								FROM [dbo].[Data] 
								WHERE [DataID] = @DataID

			IF(@DbLock = 1)
				BEGIN
					IF @DbServerName <> @ServerDnsName
						BEGIN
							IF @Count > 0
								BEGIN
									PRINT @Count
									SET @Count = @Count - 20
									ROLLBACK TRANSACTION
									WAITFOR DELAY '00:00:00.020'
									GOTO Reload
								END
							ELSE
								BEGIN
									SET @Res = 1 -- Locked Error Code
									ROLLBACK TRANSACTION
								END
						END
					ELSE
						BEGIN
							SET @Res = 0 -- OK
							ROLLBACK TRANSACTION
						END
				END
			ELSE
				BEGIN
					UPDATE [Data]
					SET  Locked				=	1
						,[ServerDnsName]	=	@ServerDnsName
					WHERE [DataID]		=	@DataID
					COMMIT TRANSACTION
					SET @Res = 0 -- OK
				END
	END
	RETURN @Res
END
GO
/****** Object:  StoredProcedure [dbo].[IsLocked]    Script Date: 12/05/2007 23:31:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Error Code
-- 0 ok
-- 1 Locked
-- 2 Not Same Class Name
-- 3 Does't Exist

CREATE PROCEDURE [dbo].[IsLocked]
(
	@ApplicationID		UNIQUEIDENTIFIER,
	@DataName		NVARCHAR(512),
	@IsLocked			bit output,
	@ServerDnsName		NVARCHAR(512) OUTPUT
)
AS
BEGIN

	DECLARE @Res INT
	DECLARE @DataID INT

	SELECT @DataID = [DataID]  FROM [dbo].[Data] WHERE [DataName] = @DataName AND [ApplicationID] = @ApplicationID

	IF @DataID IS NULL
		BEGIN
			SET @Res = 3 -- Doesn't Exists
		END
	ELSE
		BEGIN
			SELECT @IsLocked		=	[Locked]
								FROM [dbo].[Data] 
								WHERE [DataID] = @DataID
			
			SELECT @ServerDnsName	=	[ServerDnsName]
								FROM [dbo].[Data] 
								WHERE [DataID] = @DataID
			SET @Res = 0 -- OK
	END
	RETURN @Res
END
GO
/****** Object:  StoredProcedure [dbo].[LastUpdate]    Script Date: 12/05/2007 23:31:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Error Code
-- 0 ok
-- 1 Locked
-- 2 Not Same Class Name
-- 3 Does't Exist

CREATE PROCEDURE [dbo].[LastUpdate]
(
	@ApplicationID		UNIQUEIDENTIFIER,
	@DataName		NVARCHAR(512),
	@LastUpdate			TIMESTAMP OUTPUT
)
AS
BEGIN

	DECLARE @Res INT
	DECLARE @DataID INT

	SELECT @DataID = [DataID]  FROM [dbo].[Data] WHERE [DataName] = @DataName AND [ApplicationID] = @ApplicationID

	IF @DataID IS NULL
		BEGIN
			SET @Res = 3 -- Doesn't Exists
		END
	ELSE
		BEGIN			
			SELECT @LastUpdate =	[LastUpdate]
									FROM [dbo].[Data] 
									WHERE [DataID] = @DataID
			
			SET @Res = 0 -- OK
	END
	RETURN @Res
END
GO
/****** Object:  StoredProcedure [dbo].[UnlockData]    Script Date: 12/05/2007 23:31:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Error Code
-- 0 ok
-- 1 Locked
-- 2 Not Same Class Name
-- 3 Does't Exist

CREATE PROCEDURE [dbo].[UnlockData]
(
	@ApplicationID		UNIQUEIDENTIFIER,
	@DataName		NVARCHAR(512),
	@ServerDnsName		NVARCHAR(512)
)
AS
BEGIN

	DECLARE @Res INT
	DECLARE @DataID INT

	SELECT @DataID = [DataID]  FROM [dbo].[Data] WHERE [DataName] = @DataName AND [ApplicationID] = @ApplicationID

	IF @DataID IS NULL
		BEGIN
			SET @Res = 3 -- Doesn't Exists
		END
	ELSE
		BEGIN
			DECLARE @DbLock bit
			DECLARE @DbServerName NVARCHAR(512)
			
			SELECT @DbLock =	[Locked]
								FROM [dbo].[Data] 
								WHERE [DataID] = @DataID


			SELECT @DbServerName=	[ServerDnsName] 
									FROM [dbo].[Data] 
									WHERE [DataID] = @DataID
			
			IF(@DbLock = 1)
				BEGIN
					IF @DbServerName = @ServerDnsName
						BEGIN
							UPDATE [Data]
							SET  Locked				=	0
							WHERE [DataID]		=	@DataID
							
							SET @Res = 0 -- OK
						END
					ELSE
						BEGIN
							SET @Res = 1 -- Locked Error Code
						END
		
					SET @Res = 0 -- OK
				END
			ELSE
				BEGIN
					SET @Res = 0 -- OK
				END
	END
	RETURN @Res
END
GO
/****** Object:  StoredProcedure [dbo].[UnlockAllOwneData]    Script Date: 12/05/2007 23:31:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Error Code
-- 0 ok
-- 1 Locked
-- 2 Not Same Class Name
-- 3 Does't Exist

CREATE PROCEDURE [dbo].[UnlockAllOwneData]
(
	@ApplicationID		UNIQUEIDENTIFIER,
	@ServerDnsName		NVARCHAR(512)
)
AS
BEGIN			
	UPDATE	[Data]
	SET		Locked				=	0
	WHERE	[ApplicationID]		=	@ApplicationID AND  [ServerDnsName] = @ServerDnsName

	RETURN 0 -- OK
END
GO
/****** Object:  StoredProcedure [dbo].[UnlockAllData]    Script Date: 12/05/2007 23:31:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Error Code
-- 0 ok
-- 1 Locked
-- 2 Not Same Class Name
-- 3 Does't Exist

CREATE PROCEDURE [dbo].[UnlockAllData]
(
	@ApplicationID		UNIQUEIDENTIFIER
)
AS
BEGIN			
	UPDATE	[Data]
	SET		Locked				=	0
	WHERE	[ApplicationID]		=	@ApplicationID

	RETURN 0 -- OK
END
GO
